package com.vendar.quicknote;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class AccountDbManager {
	public final String DB_NAME = "account.db";
	public final String DB_OAUTH_ACCOUNT_TABLE = "oauth_accounts";
	public final String DB_EMAIL_ACCOUNT_TABLE = "email_accounts";
	
	public final int DB_VERSION = 2;
	
	public final String KEY_ID = "_id";
	
	public static final String KEY_VENDOR = "vendor";
	public static final String KEY_USERID = "user_id";
	public static final String KEY_TOKEN = "token";
	public static final String KEY_SECRET = "secret";
	
	public static final String KEY_EMAIL = "email";
	public static final String KEY_PASSWORD = "password";
	public static final String KEY_SMTP_HOST = "smtp_host";
	public static final String KEY_SMTP_PORT = "smtp_port";
	
	private SQLiteDatabase mDb = null;
	private DbHelper mDbHelper = null;
	
	public AccountDbManager(Context context){
		this.mDbHelper = new DbHelper(context);
		this.mDb = this.mDbHelper.getWritableDatabase();
	}
	
	/*
	 * save or update an oauth account.
	 */
	public boolean SaveOAuthAccount(String vendor,String userId,String token,String secret){
		if( vendor == null ||
			userId == null ||
			token == null ||
			secret == null ){
			return false;
		}
		
		ContentValues args = new ContentValues();
		args.put(KEY_VENDOR, vendor);
		args.put(KEY_USERID, userId);
		args.put(KEY_TOKEN, token);
		args.put(KEY_SECRET, secret);
		
		String wherestr = KEY_VENDOR+"=? and "+KEY_USERID+"=?";
		String whereargs[] = new String[]{vendor,userId};
		Cursor cur = mDb.query(DB_OAUTH_ACCOUNT_TABLE, 
				new String[]{KEY_VENDOR,KEY_USERID,KEY_TOKEN,KEY_SECRET},
				wherestr, whereargs, null, null, null);
		if (cur == null || cur.getCount()<=0){
			//create
			return (mDb.insert(DB_OAUTH_ACCOUNT_TABLE, null, args)>=0);
		}else{
			//update
			return (mDb.update(DB_OAUTH_ACCOUNT_TABLE, args, wherestr, whereargs)>0);
		}
	}
	
	/*
	 * retrieve an oauth account if exists.
	 */
	public Cursor RetrieveOAuthAccount(String vendor){
		Cursor res = mDb.query(DB_OAUTH_ACCOUNT_TABLE, 
				new String[]{KEY_VENDOR,KEY_USERID,KEY_TOKEN,KEY_SECRET},
				KEY_VENDOR+"=?",new String[]{vendor}, null, null, null);
		if(res != null){
			res.moveToFirst();
		}
		return res;
	}
	
	/*
	 * retrieve an oauth account with specified vendor and userid.
	 */
	public Cursor RetrieveOAuthAccount(String vendor,String userId){
		Cursor res = mDb.query(DB_OAUTH_ACCOUNT_TABLE, 
				new String[]{KEY_VENDOR,KEY_USERID,KEY_TOKEN,KEY_SECRET},
				KEY_VENDOR+"=? and "+KEY_USERID+"=?",
				new String[]{vendor,userId}, null, null, null);
		if(res != null){
			res.moveToFirst();
		}
		return res;
	}
	
	/*
	 * retrieve all oauth accounts.
	 */
	public Cursor RetrieveOAuthAccount(){
		Cursor res = mDb.query(DB_OAUTH_ACCOUNT_TABLE,
				new String[]{KEY_VENDOR,KEY_USERID,KEY_TOKEN,KEY_SECRET}, 
				null, null, null, null, null);
		if( res != null ){
			res.moveToFirst();
		}
		return res;
	}
	
	/*
	 * save or update an email account.
	 */
	public boolean SaveEMailAccount(String username,String password,String smtpHost,String smtpPort){
		if( username == null
		|| password == null
		|| smtpHost == null
		|| smtpPort == null ){
			return false;
		}
		ContentValues args = new ContentValues();
		args.put(KEY_EMAIL, username);
		args.put(KEY_PASSWORD, password);
		args.put(KEY_SMTP_HOST, smtpHost);
		args.put(KEY_SMTP_PORT, smtpPort);
		
		String wherestr = KEY_EMAIL + "=?";
		Cursor res = mDb.query(DB_EMAIL_ACCOUNT_TABLE, new String[]{KEY_ID},
				wherestr, new String[]{username}, null, null, null);
		
		if( res == null || res.getCount() <= 0 ){
			return (mDb.insert(DB_EMAIL_ACCOUNT_TABLE, null, args)>=0);
		}else{
			return (mDb.update(DB_EMAIL_ACCOUNT_TABLE, args, wherestr, new String[]{username})>=0);
		}
	}
	
	/*
	 * retrieve all email account.
	 */
	public Cursor RetrieveEMailAccount(){
		Cursor res = mDb.query(DB_EMAIL_ACCOUNT_TABLE, 
				new String[]{KEY_EMAIL,KEY_PASSWORD,KEY_SMTP_HOST,KEY_SMTP_PORT},
				null, null, null, null, null);
		if( res != null ){
			res.moveToFirst();
		}
		return res;
	}
	
	/*
	 * retrieve an email account with specified email username.
	 */
	public Cursor RetrieveEMailAccount(String username){
		Cursor res = mDb.query(DB_EMAIL_ACCOUNT_TABLE, 
				new String[]{KEY_EMAIL,KEY_PASSWORD,KEY_SMTP_HOST,KEY_SMTP_PORT},
				KEY_EMAIL+"=?", new String[]{username}, null, null, null);
		if( res != null ){
			res.moveToFirst();
		}
		return res;
	}
	
	/*
	 * the database open helper
	 */
	class DbHelper extends SQLiteOpenHelper{
		public DbHelper(Context context){
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			//create oauth account table;
			db.execSQL("create table "+
					DB_OAUTH_ACCOUNT_TABLE + "("+
					KEY_ID + " integer primary key autoincrement, "+
					KEY_VENDOR + " text not null, "+
					KEY_USERID + " text not null, " + 
					KEY_TOKEN + " text not null," + 
					KEY_SECRET + " text not null);");
			//create email account table;
			db.execSQL("create table "+
					DB_EMAIL_ACCOUNT_TABLE + "("+
					KEY_ID + " integer primary key autoincrement, "+
					KEY_EMAIL + " text not null, "+
					KEY_PASSWORD + " text not null, "+
					KEY_SMTP_HOST + " text not null, "+
					KEY_SMTP_PORT + " text not null);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			//just drop old then create new one...
			db.execSQL("drop table if exists "+DB_OAUTH_ACCOUNT_TABLE+";");
			db.execSQL("drop table if exists "+DB_EMAIL_ACCOUNT_TABLE+";");
			onCreate(db);
		}
	}
}
